﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace BAITAP2_LTCSDL
{
    public partial class Form1 : Form
    {
        private SqlConnection con;
        private SqlDataAdapter da = new SqlDataAdapter();
        SqlCommand cmd = new SqlCommand();
        private DataTable dt = new DataTable("HOSOHS");

        public void connect()
        {
            string conn = "Data Source=(local);Integrated Security = true; Database=QUANLIHS_CAP3";
            try
            {
                con = new SqlConnection(conn);
                con.Open();
                //con = null;
            }
            catch (Exception ex)
            {
                MessageBox.Show("khong ket noi duoc database");
            }
        }
        public void disconnect()
        {
            con.Close();
            con.Dispose();
        }

        public Form1()
        {
            InitializeComponent();
            connect();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            connect();
            getdata();
            binding();
        }
        private void getdata()
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = @"select * from HOSOHS ";
            da.SelectCommand = cmd;
            da.Fill(dt);
            dataGridView1.DataSource = dt;

        }

        private void binding()
        {
            txt_mahs.DataBindings.Clear();
            txt_mahs.DataBindings.Add("Text", dataGridView1.DataSource, "MAHS");
            txt_hoten.DataBindings.Clear();  
            txt_hoten.DataBindings.Add("Text", dataGridView1.DataSource, "HOTEN");
           dateTimePicker1.DataBindings.Clear();

           dateTimePicker1.DataBindings.Add("Value", dataGridView1.DataSource, "NGAYSINH");
            comboBox1.DataBindings.Clear();
            comboBox1.DataBindings.Add("Text", dataGridView1.DataSource, "GIOITINH");
            txt_diachi.DataBindings.Clear();
            txt_diachi.DataBindings.Add("Text", dataGridView1.DataSource, "DIACHI");
            txt_email.DataBindings.Clear();
            txt_email.DataBindings.Add("Text", dataGridView1.DataSource, "EMAIL");
        }
        private void btn_exit_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }

        private void btn_xoahs_Click(object sender, EventArgs e)
        {
            DataRow row = dt.Select("MAHS=" + Convert.ToInt32(txt_mahs.Text))[0];
            row.BeginEdit();
            row.Delete();
            row.EndEdit();
            SqlCommand cmdDelete = new SqlCommand();
            cmdDelete.Connection = con;
            cmdDelete.CommandType = CommandType.Text;
            cmdDelete.CommandText = "Delete From HOSOHS Where  MAHS=@MAHS";
            cmdDelete.Parameters.Add("@MAHS",SqlDbType.Int,50,"MAHS");
            da.DeleteCommand = cmdDelete;
            da.Update(dt);
            MessageBox.Show("ban da xoa thanh cong!");


        }

        private void btn_suahs_Click(object sender, EventArgs e)
        {
            DataRow row = dt.Select("MAHS=" + Convert.ToInt32(txt_mahs.Text))[0];
            row.BeginEdit();
            row["HOTEN"] = txt_hoten.Text;
            row["GIOITINH"] = comboBox1.Text;
            row["DIACHI"] = txt_diachi.Text;
            row["EMAIL"] = txt_email.Text;
            row["NGAYSINH"] = dateTimePicker1.Value;
            row.EndEdit();
            SqlCommand cmdUpdate = new SqlCommand();
            cmdUpdate.Connection=con;
            cmdUpdate.CommandType=  CommandType.Text;
            cmdUpdate.CommandText=@"Update HOSOHS Set MAHS=@MAHS,
                HOTEN=@HOTEN,GIOITINH=@GIOITINH,DIACHI=@DIACHI,EMAIL=@EMAIL,NGAYSINH=@NGAYSINH Where MAHS=@MAHS";
             cmdUpdate.Parameters.Add("@MAHS", SqlDbType.NChar, 10).Value = txt_mahs.Text;
            cmdUpdate.Parameters.Add("@HOTEN", SqlDbType.NVarChar, 50).Value = txt_hoten.Text;
             cmdUpdate.Parameters.Add("@GIOITINH", SqlDbType.NChar, 10).Value = comboBox1.Text;
            cmdUpdate.Parameters.Add("@NGAYSINH", SqlDbType.Date).Value = dateTimePicker1.Text;
             cmdUpdate.Parameters.Add("@DIACHI", SqlDbType.NVarChar, 50).Value = txt_diachi.Text;
             cmdUpdate.Parameters.Add("@EMAIL", SqlDbType.NVarChar, 50).Value = txt_email.Text;
            da.UpdateCommand=cmdUpdate;
            da.Update(dt);
            MessageBox.Show("ban da Update thanh cong");

        }

        private void btn_cap_nhat_hs_Click_1(object sender, EventArgs e)
        {
            ThemHS frmThem = new ThemHS();
            frmThem.Show();
            Hide();
        }
    }
    }

